﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-16
修改:
修改时间: 
功能: 店铺更新系数
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using com.nm.library;
using Nancy.Extensions;
using VerticaAPI;

namespace PlanAPI
{
    [Description("店铺更新系数")]
    public class PLBasShopUpdateRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-03-15
        [MustLogin]
        [Description("店铺更新系数列表")]
        [ReturnType(typeof(PaperData<PLBasShopUpdateRateModel.ListModel>))]
        public ApiResult GetPLBasShopUpdateRateList([ParamDescription("商品区域")] string ParentAreaId = "", [ParamDescription("店铺")] string ShopIDs = "", [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
                var count = ReturnPLBasShopUpdateRateCount(ParentAreaId, shopList);
                var dt = ReturnPLBasShopUpdateRateList(ParentAreaId, shopList, Page, Size);
                var result = PaperData<PLBasShopUpdateRateModel.ListModel>.Create(count, Size, Page);
                var list = ModelConvertHelper<PLBasShopUpdateRateModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 店铺更新系数列表
        /// </summary>
        /// <param name="parentAreaId"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns>返回店铺更新系数列表</returns>
        /// Writer：梁家健
        /// Create Date：2017-03-16
        public DataTable ReturnPLBasShopUpdateRateList(string ParentAreaId, string ShopList, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  * ,
        dictName AS ParentAreaName
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY rate.Yearv DESC, rate.ParentAreaID, rate.ShopID, rate.Monthv DESC, rate.Weekv DESC ) AS Row
          FROM      ngoods.ngoods_PL_Bas_ShopUpdateRate rate
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_Shop
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) shop ON rate.ShopID = shop.ShopID
                                         AND rate.ExtrSource = shop.ExtrSource
                                         AND rate.CompanyID = shop.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_global_dictionary
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) dict ON rate.ParentAreaID = dict.dictValue
                                        AND rate.ExtrSource = dict.ExtrSource
                                        AND rate.CompanyID = dict.CompanyID
          WHERE     rate.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND rate.CompanyID = '{UserInfo.UserCompanyId}'
                    {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and ParentAreaID = '{ParentAreaId}'")} 
                    {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in({ShopList})")} 
        ) table1
WHERE   Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page} * {Size}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        /// <summary>
        /// 统计店铺更新系数总条数
        /// </summary>
        /// <param name="ParentAreaId"></param>
        /// <param name="Keys"></param>
        /// <returns>返回店铺更新系数总条数</returns>
        /// Writer：梁家健
        /// Create Date：2017-03-16
        public int ReturnPLBasShopUpdateRateCount(string ParentAreaId, string ShopList)
        {
            try
            {
                var sql = $@"SELECT    COUNT(1)
          FROM      ngoods.ngoods_PL_Bas_ShopUpdateRate rate
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_Shop
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) shop ON rate.ShopID = shop.ShopID
                                         AND rate.ExtrSource = shop.ExtrSource
                                         AND rate.CompanyID = shop.CompanyID
                    INNER JOIN ( SELECT *
                                 FROM   ndw.ndw_global_dictionary
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                        AND CompanyID = '{UserInfo.UserCompanyId}'
                               ) dict ON rate.ParentAreaID = dict.dictValue
                                        AND rate.ExtrSource = dict.ExtrSource
                                        AND rate.CompanyID = dict.CompanyID
          WHERE     rate.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND rate.CompanyID = '{UserInfo.UserCompanyId}'
                    {(string.IsNullOrEmpty(ParentAreaId) ? "" : $@" and ParentAreaID = '{ParentAreaId}'")} 
                    {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in({ShopList})")} ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-16
        [JsonType]
        [MustLogin]
        [Description("修改店铺更新系数")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdatePLBasShopUpdateRate(PLBasShopUpdateRateModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (param.list != null)
                {
                    param.list.ToList().ForEach(d =>
                    {
                        d.CompanyID = UserInfo.UserCompanyId;
                        d.ExtrSource = UserInfo.UserExtrSource;
                        d.CheckState = 1;
                    });
                    MergePLBasShopGrossRate(param.list);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 枚举
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void MergePLBasShopGrossRate(IList<PLBasShopUpdateRateModel.NeceModel> list)
        {
            try
            {
                var uid = Guid.NewGuid().ToString().Replace("-", "");
                var tempTableName = $" ngoods.ngoods_{uid} ";
                const string targetTableName = @" ngoods.ngoods_PL_Bas_ShopUpdateRate ";
                var dt =
                    DataTableHelper.LinqToDataTable(
                        list.DistinctBy(d => new { d.CompanyID, d.Yearv, d.Weekv, d.Monthv, d.ParentAreaID, d.ShopID }));
                const string onConditions = @" targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.ExtrSource=sourceTable.ExtrSource
         AND targetTable.Yearv=sourceTable.Yearv
         AND targetTable.Monthv=sourceTable.Monthv
         AND targetTable.Weekv=sourceTable.Weekv
         AND targetTable.ParentAreaID=sourceTable.ParentAreaID
         AND targetTable.ShopID=sourceTable.ShopID ";
                const string setConditions = @" ShopRate = sourceTable.ShopRate/100 ,
                                            CheckState = sourceTable.CheckState ,
                                            Remark = sourceTable.Remark ";
                const string insertConditions = @" CompanyID ,
                      ExtrSource ,
                      Yearv ,
                      Monthv ,
                      Weekv ,
                      ParentAreaID ,
                      ShopID ,
                      ShopRate ,
                      CheckState ,
                      Remark ";
                const string valuesConditions = @" sourceTable.CompanyID ,
                      sourceTable.ExtrSource ,
                      sourceTable.Yearv ,
                      sourceTable.Monthv ,
                      sourceTable.Weekv ,
                      sourceTable.ParentAreaID ,
                      sourceTable.ShopID ,
                      sourceTable.ShopRate/100 ,
                      sourceTable.CheckState ,
                      sourceTable.Remark ";
                Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-16
        public DataTable ExportPLBasShopUpdateRate(string Keys)
        {
            try
            {
                var sql = $@"SELECT  rate.CompanyID ,
        rate.Yearv ,
        rate.Weekv ,
        rate.Monthv ,
        dictName AS ParentAreaName ,
        rate.ShopID ,
        shop.ShopCode ,
        shop.ShopName ,
        rate.ShopRate
FROM    ngoods.ngoods_PL_Bas_ShopUpdateRate rate
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_global_dictionary
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) dict ON rate.ParentAreaID = dict.dictValue
                             AND rate.ExtrSource = dict.ExtrSource
                             AND rate.CompanyID = dict.CompanyID
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) shop ON rate.ShopID = shop.ShopID
                             AND rate.ExtrSource = shop.ExtrSource
                             AND rate.CompanyID = shop.CompanyID
WHERE   rate.ExtrSource = '{UserInfo.UserExtrSource}'
        AND rate.CompanyID = '{UserInfo.UserCompanyId}'
        AND Yearv = {DateTime.Now.Year} 
		{(string.IsNullOrEmpty(Keys) ? "" : Keys)}
ORDER BY rate.Yearv DESC ,
        rate.Weekv DESC ,
        rate.Monthv DESC ,
        rate.ParentAreaID ,
        rate.ShopID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void CreateTempTable(DataTable dt, string TempTableName,Dictionary<string,string> Cesd)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ExtrSource varchar(30),
    Yearv int,
    Monthv int,
    Weekv int,
    ParentAreaName varchar(80),
    ShopID varchar(30),
    ShopCode varchar(30),
    ShopName varchar(120),
    ShopRate numeric(8,4),
    Remark varchar(150)
)";
                dt.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                foreach (DataRow item in dt.Rows)
                {
                    var companyID = item["CompanyID"].ToString();
                    item["ExtrSource"] = Cesd.Keys.Contains(companyID) ? Cesd[companyID] : "";
                }
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入店铺更新系数
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public DataTable ImportPLBasShopUpdateRate(string TempTableName, Dictionary<string, string> Cesd)
        {
            try
            {
                var sql = $@"SELECT  temp.CompanyID ,
        temp.Yearv ,
        temp.Monthv ,
        temp.Weekv ,
        temp.ParentAreaName ,
        temp.ShopID ,
        temp.ShopRate ,
        temp.Remark ,
        dict.dictValue AS ParentAreaID ,
        shop.ShopCode ,
        shop.ShopName
FROM    {TempTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_global_dictionary
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) dict ON temp.ParentAreaName = dict.dictName
                            AND temp.ExtrSource = dict.ExtrSource
                            AND temp.CompanyID = dict.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON temp.ShopID = shop.ShopID
                            AND temp.ExtrSource = shop.ExtrSource
                            AND temp.CompanyID = shop.CompanyID";
                var companyID = UserInfo.UserCompanyId;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasShopUpdateRateModel.ListModel>.ConvertToModel(dt);
                var newList =
                    list.Where(
                        d =>
                            d.CompanyID == companyID && Cesd.Keys.Contains(d.CompanyID) &&
                            d.ParentAreaID != null && d.ShopCode != null && d.ShopRate >= 0 && d.ShopRate <= 100);
                var resultList = new List<PLBasShopUpdateRateModel.ListModel>();
                foreach (var item in newList)
                {
                    var begin = CalendarHandler.GetWeekOfYear(new DateTime((int)item.Yearv, (int)item.Monthv, 1));
                    var end =
                        CalendarHandler.GetWeekOfYear(
                            new DateTime((int)item.Yearv, (int)item.Monthv, 1).AddMonths(1).AddDays(-1));
                    item.ExtrSource = Cesd[item.CompanyID];
                    for (var i = begin; i <= end; i++)
                    {
                        if (item.Weekv != i) continue;
                        resultList.Add(item);
                        break;
                    }
                }
                MergePLBasShopGrossRate(resultList.Select(d => new PLBasShopUpdateRateModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    ExtrSource = d.ExtrSource,
                    Yearv = d.Yearv,
                    Monthv = d.Monthv,
                    Weekv = d.Weekv,
                    ParentAreaID = d.ParentAreaID,
                    ShopID = d.ShopID,
                    ShopRate = d.ShopRate,
                    Remark = d.Remark,
                    CheckState = 1L,
                }).ToList());
                var errorList = list.Except(resultList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                //if (errorDT.Rows.Count > 0)
                //{
                //    errorDT.Columns.Remove("ParentAreaID");
                //}
                return errorDT;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 根据年份，月份返回店铺更新系数
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-20
        public IList<PLBasShopUpdateRateModel.NeceModel> ReturnAllPLBasShopUpdateRate(int Yearv,int Monthv)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ngoods.ngoods_PL_Bas_ShopUpdateRate
WHERE   Yearv = {Yearv}
        AND Monthv = {Monthv}
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}' ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLBasShopUpdateRateModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
